if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCompleteQuestionnaireAnswerList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCompleteQuestionnaireAnswerList]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* Name:			GetCompleteQuestionnaireAnswerList
* Purpose:		Retrieves QuestionnaireAnswers tied to a ProtocolVersion
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @protocolVersionId	The protocolversion's id whose answers have to be retrieved
******************************************************************/

Create Procedure [dbo].[GetCompleteQuestionnaireAnswerList] (    
	@protocolVersionId  INTEGER,  
	@moduleName varchar(75)  
)    
AS    
BEGIN    
    
	SET NOCOUNT ON    
    
	declare @questCategoryName as VARCHAR(9)    
	set @questCategoryName = 'QUESTCAT%'    

	declare @moduleBit int  
	Select @moduleBit = BitValue from ModuleType where rtrim(ltrim(Name)) = rtrim(ltrim(@moduleName))  
    
	Select  Q.[Name],     
		Q.Category as QuestionnaireCategory ,    
		0 as Required ,    
		Q.AllowMultiple,     
		QV.Id as QuestionnaireVersionId,     
		0 as ProtocolVersionId,    
		0 as Editable,    
		0 as [Id],    
		null as LastModifiedDate,    
		0 as ExternalId,    
		0 as IsClone,     
		2 as ActionTypeId, -- Action = Edit    
		0 as Highlight,    
		null as LastModifiedbyAdmPersonId,    
		null as LastModifiedBy,  
		Q.ModuleType    
	From Questionnaire Q    
		inner join QuestionnaireVersion QV on Q.[Id] = QV.QuestionnaireId and (Q.moduleType & @moduleBit)<> 0      
	WHERE Q.Category like @questCategoryName    
		AND QV.Active = 1    
		AND (Q.[Id] not in (select Q1.[Id] 
							From Questionnaire Q1    
								INNER JOIN QuestionnaireVersion QV1 ON Q1.[Id] = QV1.QuestionnaireId    
								INNER JOIN QuestionnaireAnswer QA on QV1.[Id] = QA.QuestionnaireVersionId     
								INNER JOIN ProtocolVersion_QuestionnaireAnswer_Map PQM ON (PQM.QuestionnaireAnswerId = QA.Id AND PQM.ProtocolVersionId = @protocolVersionId)
							)    
			OR Q.AllowMultiple = 1 
			)    
	order by Q.Type    
      
END    

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  